/**************************************************************************
	Replication do-file: "Corruption in Customs"
	Cyril Chalendard, Ana Fernandes, Gael Raballand and Bob Rijkers
	
	Created on: 01/07/2022
**************************************************************************/

clear all
set more off, perm
cap log close
cls

* ----------------------- DIRECTORIES AND FOLDERS ----------------------- *

* Directories in which data are stored
global main "PUT YOUR DIRECTORY PATH HERE"
cd "$folder"

* Folders
global outputdata = "$main\Output Data"
cap mkdir "$main\Tables"
global tables = "$main\Tables"

* ----------------------------- BEGINS HERE ----------------------------- *

* --------
* Table A6
* --------

* Matrix
mat tableA6 = J(5,8,.)

* Coefficients
local vars `" "insp_f" "key_dec" "'
local names `" "inspector" "broker" "'
forvalues i = 1/2 {

	local x: word `i' of `vars'
	local y: word `i' of `names'

	* Dataset
	use "$outputdata\CFRR.dta", clear // open dataset
	** Sample
	gen double date = mdy(month_reg, day_reg, year_reg) 
	format date %td // set date format
	drop if dow(date)==0 | dow(date)==6 // drop weekends
	** Collapse
	gen dec = 1 // create constant for future collapse
	collapse (sum) dec , by(`x' date) // sum declarations and days worked by inspector(broker)-date
	gen worked = 1 // create constant
	** Panel
	encode `x' , gen(`y') // create a categoric variable to balance the panel
	xtset `y' date // set dataset as a panel
	tsfill , full // balance panel
	drop if dow(date)==0 | dow(date)==6 // drop weekends
	foreach x of varlist dec worked {
		replace `x' = 0 if `x'==.
	} // Loop: replace missings for zeros
	** Dates
	gen week = week(date) // get week of the year
	gen year = year(date) // get year
	gen dow = dow(date) // get day of the week

	* Percentage of worked weekdays
	preserve
	sort `y' week year // sort observations
	bys `y' week year : egen days_worked_week = sum(worked) // create a variable that contains the number of days worked by week
	drop if days_worked_week==0 // drop weeks with no worked days
	bys `y' dow : gen weeks = _n // create a counter of weeks
	collapse (sum) worked (max) weeks , by(`y' dow) // sum worked days and keep the maximum value of weeks by inspector(broker) and dow
	reshape wide worked weeks , i(`y') j(dow) // reshape to have the inspectors(brokers) as level of observation
	forvalues i = 1/5 {
		replace worked`i' = worked`i'/weeks`i'
	} // Loop: create percentages
	collapse (mean) worked* (sd) workedsd1=worked1 workedsd2=worked2 workedsd3=worked3 workedsd4=worked4 workedsd5=worked5 // take the mean and sd of the worked days for all inspectors(brokers)
	gen id = 1 // create a constant for future reshape
	reshape long worked workedsd , i(id) j(dow) // reshape to have the dow as level of observation
	drop id // drop constant
	rename (worked workedsd) (mean sd) // rename variables
	tostring dow , replace // convert dow to string
	local days `" "Monday" "Tuesday" "Wednesday" "Thursday" "Friday" "'
	forvalues i = 1/5 {
		local x: word `i' of `days'
		replace dow = "`x'" if dow=="`i'"
	} // Loop: replace values of dow
	mkmat mean sd, mat(`y'_worked) rown(dow) // create a matrix with the variables values
	restore
	
	* Percentage of declarations submitted by weekday
	preserve
	collapse (sum) dec , by(`y' dow) // sum declarations and days worked by inspector(broker)-dow
	reshape wide dec , i(`y') j(dow) // reshape to have the inspectors(brokers) as level of observation
	egen tot_decs = rsum(dec*) // create the sum of declarations
	for var dec* : replace X = X/tot_decs // create percentages
	collapse (mean) dec* (sd) decsd1=dec1 decsd2=dec2 decsd3=dec3 decsd4=dec4 decsd5=dec5 // take the mean and sd of the declarations for all inspectors(brokers)
	gen id = 1 // create a constant for future reshape
	reshape long dec decsd , i(id) j(dow) // reshape to have the dow as level of observation
	drop id // drop constant
	rename (dec decsd) (mean sd) // rename variables
	tostring dow , replace // convert dow to string
	local days `" "Monday" "Tuesday" "Wednesday" "Thursday" "Friday" "'
	forvalues i = 1/5 {
		local x: word `i' of `days'
		replace dow = "`x'" if dow=="`i'"
	} // Loop: replace values of dow
	mkmat mean sd, mat(`y'_dec) rown(dow) // create a matrix with the variables values
	restore
}

* Insert results in final matrix
local j = 1
local names `" "inspector" "broker" "'
forvalues i = 1/2 {
	local x: word `i' of `names'
	
	mat tableA6[1,`j'] = `x'_worked
	local j = `j'+2
	mat tableA6[1,`j'] = `x'_dec
	local j = `j'+2
}


* Export
* ------
putexcel set "$tables\Table A6.xlsx", replace sheet(TableA6) // create a new excel spreadsheet

* Main titles
putexcel (B1:E1), merge hcenter vcenter bold
putexcel B1 = "Inspectors"
putexcel (F1:I1), merge hcenter vcenter bold
putexcel F1 = "Brokers"

* Secondary titles
local in `" "B" "D" "F" "H" "'
local fi `" "C" "E" "G" "I" "'
forvalues i = 1/4 {
	
	local x: word `i' of `in'
	local y: word `i' of `fi'
	
	putexcel (`x'2:`y'2), merge hcenter vcenter bold
	
	if "`i'" == "1" | "`i'" == "3" {
		putexcel `x'2 = "Weekdays worked"
	}
	
	else {
		putexcel `x'2 = "Declarations"
	}
}

* Column names
local cells `" "B" "C" "D" "E" "F" "G" "H" "I" "'
local labels `" "Average" "Std. dev." "Average" "Std. dev." "Average" "Std. dev." "Average" "Std. dev."  "'
forvalues i = 1(1)8 {

	local x: word `i' of `labels'
	local y: word `i' of `cells'

	putexcel `y'3 = "`x'", hcenter vcenter
}

* Row names
local j = 4
local days `" "Monday" "Tuesday" "Wednesday" "Thursday" "Friday" "'
forvalues i = 1/5 {
	local x: word `i' of `days'
	
	putexcel A`j' = "`x'", left vcenter
	local j = `j'+1
}

* Coefficients
putexcel B4 = matrix(tableA6), nformat(0.0%) hcenter vcenter


* -------------------------------- ENDS HERE -------------------------------- *